from sqlalchemy.orm import Session
from sqlalchemy.exc import SQLAlchemyError
from models import Conversation, Message
from sqlalchemy import desc

def create_conversation(db: Session, title: str, user_id: int):
    try:
        db_conversation = Conversation(title=title, creator_id=user_id)
        db.add(db_conversation)
        db.commit()
        db.refresh(db_conversation)
        return db_conversation
    except SQLAlchemyError as e:
        db.rollback()
        return {"error": str(e), "code": 500}

def get_conversation(db: Session, conversation_id: int, user_id: int = None):
    try:
        conversation = db.query(Conversation).filter(
            Conversation.id == conversation_id,
            Conversation.creator_id == user_id
            ).first()
        if conversation is None:
            return {"error": "Conversation not found", "code": 404}
        return conversation
    except SQLAlchemyError as e:
        return {"error": str(e), "code": 500}

def get_conversations(db: Session, user_id: int, skip: int = 0, limit: int = 10):
    try:
        return db.query(Conversation).filter(
            Conversation.creator_id == user_id
        ).order_by(desc(Conversation.id)).offset(skip).limit(limit).all()
    except SQLAlchemyError as e:
        return {"error": str(e), "code": 500}

def delete_conversation(db: Session, conversation_id: int, user_id: int):
    try:
        db_conversation = db.query(Conversation).filter(
            Conversation.id == conversation_id,
            Conversation.creator_id == user_id,
            ).first()
        if db_conversation is None:
            return {"error": "Conversation not found", "code": 404}
        db.delete(db_conversation)
        db.commit()
        return db_conversation.id
    except SQLAlchemyError as e:
        db.rollback()
        return {"error": str(e), "code": 500}

def create_message(db: Session, conversation_id: int, content: str, user_id: int, role: str = "user"):
    try:
        db_message = Message(
            role=role,
            content=content, 
            user_id=user_id,
            conversation_id=conversation_id)
        db.add(db_message)
        db.commit()
        db.refresh(db_message)
        return db_message
    except SQLAlchemyError as e:
        db.rollback()
        return {"error": str(e), "code": 500}

def get_messages(db: Session, conversation_id: int, user_id: int):
    try:
        return db.query(Message).filter(
            Message.conversation_id == conversation_id,
            Message.user_id == user_id,
            ).all()
    except SQLAlchemyError as e:
        return {"error": str(e), "code": 500}

def delete_message(db: Session, message_id: int, user_id: int):
    try:
        db_message = db.query(Message).filter(
            Message.id == message_id,
            Message.user_id == user_id,
            ).first()
        if db_message is None:
            return {"error": "Message not found", "code": 404}
        db.delete(db_message)
        db.commit()
        return db_message
    except SQLAlchemyError as e:
        db.rollback()
        return {"error": str(e), "code": 500}
    
def delete_message_by_conversation(db: Session, conversation_id: int, user_id: int):
    try:
        db_messages = db.query(Message).filter(
            Message.conversation_id == conversation_id,
            Message.user_id == user_id,
            )
        if db_messages is None:
            return {"message": "Messages deleted successfully"}
        
        for db_message in db_messages:
            db.delete(db_message)
            db.commit()
        return {"message": "Messages deleted successfully"}
    except SQLAlchemyError as e:
        db.rollback()
        return {"error": str(e), "code": 500}
